﻿#include "database.h"

#include <QDebug>
#include <QUuid>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>

Database::Database(QObject *parent) : QObject(parent)
{
//    QStringList drivers = QSqlDatabase::drivers();
//    qInfo() << "Available drivers size:" << drivers.size();
//    foreach (QString driver, drivers)
//    {
//        qInfo() << "Driver:" << driver.toStdString().c_str();
//    }
}

Database::~Database()
{
    m_db.close();
}

QString Database::getXorEncryptDecrypt(const QString &str, char key)
{
    QByteArray data = str.toLatin1();
    int size = data.size();
    for (int i = 0; i < size; i++) {
        data[i] = data[i] ^ key;
    }
    return QLatin1String(data);
}

bool Database::connectToDb(DataBaseType type, const MSS &info)
{
    bool ret = false;
    if (type == DB_MYSQL)
    {
        ret = connectToMySql(info["host"], info["port"].toInt(), info["dbName"], info["username"], info["password"]);
    }
    else if (type == DB_SQLSERVER)
    {
        QString dsn = QString("DRIVER={SQL SERVER};SERVER=%1;DATABASE=%2;UID=%3;PWD=%4;")
                        .arg(info["host"]).arg(info["dbName"]).arg(info["username"]).arg(info["password"]);
        ret = connectToMsodbc(dsn);
    }
    else if (type == DB_ACCESS)
    {
        QString dsn = QString("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
                                  "FIL={MS Access};DBQ=%1").arg(info["path"]);
        ret = connectToMsodbc(dsn);
    }
    else if (type == DB_SQLITE)
    {
        ret = connectToSqlite(info["path"]);
    }
    return ret;
}

bool Database::connectToMsodbc(const QString &dsn)
{
    qInfo() << "DSN:" << dsn;
    QUuid uuid = QUuid::createUuid();
    m_db = QSqlDatabase::addDatabase("QODBC", QString("odbc-%1").arg(uuid.toString()));
    //设置MSSQLSERVER的ODBC数据源连接字符串
    //例如DRIVER={SQL SERVER};SERVER=172.16.13.99,1433;DATABASE=autoCheckDB1;UID=sa;PWD=;
    //DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=xxx.mdb
    m_db.setDatabaseName(dsn);
    //FIXME:设置超时时间避免阻塞,最小只能到5秒
    m_db.setConnectOptions("SQL_ATTR_LOGIN_TIMEOUT=5;SQL_ATTR_CONNECTION_TIMEOUT=5");
    if (!m_db.open()) {
        m_db.setConnectOptions();  //清空连接字符串
        m_lastError = m_db.lastError().text();
        qInfo() << "DB open last error:" << m_lastError.toStdString().c_str();
        return false;
    }
    m_msDsn = dsn;
    return true;
}

bool Database::connectToMySql(const QString &host, int port, const QString &dbName, const QString &username, const QString &password)
{
    if (!m_msDsn.isEmpty())
    {
        qDebug()<< "MS ODBC DB already connected";
        return false;
    }
    QUuid uuid = QUuid::createUuid();
    m_db = QSqlDatabase::addDatabase("QMYSQL", QString("mysql-%1").arg(uuid.toString()));
    m_db.setHostName(host);
    m_db.setPort(port);
    m_db.setDatabaseName(dbName);
    m_db.setUserName(username);
    m_db.setPassword(password);
    m_db.setConnectOptions("MYSQL_OPT_RECONNECT=1"); //FIXME:支持断线重连
    if (!m_db.open())
    {
        m_db.setConnectOptions();  //清空连接字符串
        m_lastError = m_db.lastError().text();
        qDebug() << "DB open last error:" << m_lastError.toStdString().c_str();
        return false;
    }
    return true;
}

bool Database::connectToSqlite(const QString &path)
{
    QUuid uuid = QUuid::createUuid();
    m_db = QSqlDatabase::addDatabase("QSQLITE", QString("sqlite-%1").arg(uuid.toString()));
    m_db.setDatabaseName(path);
    if (!m_db.open())
    {
        m_lastError = m_db.lastError().text();
        qDebug() << "DB open last error:" << m_lastError.toStdString().c_str();
        return false;
    }
    return true;
}

bool Database::executeSql(const QString &strSql)
{
    qInfo() << "Execute SQL statement:" << strSql.toStdString().c_str();
    if (m_db.isOpen()) {
        QSqlQuery sqlQuery(m_db);
        if (sqlQuery.exec(strSql)) {
            return true;
        } else {
            m_lastError = sqlQuery.lastError().text();
            int sqlQueryLastErrorNumber = sqlQuery.lastError().number();
            qInfo() << "Sql query last error code:"
                      << sqlQueryLastErrorNumber << ",text:"
                      << m_lastError.toStdString().c_str();
        }
    }
    else
    {
        m_lastError = "mdb not open";
        qInfo() << m_lastError;
    }
    return false;
}

bool Database::executeSqlWithResult(const QString &strSql, QVariant &result)
{
    qInfo() << "Execute SQL statement:" << strSql.toStdString().c_str();
    if (m_db.isOpen()) {
        QSqlQuery sqlQuery(m_db);
        if (sqlQuery.exec(strSql)) {
            while (sqlQuery.next()) {
                result = sqlQuery.value(0);
            }
            return true;
        } else {
            m_lastError = sqlQuery.lastError().text();
            int sqlQueryLastErrorNumber = sqlQuery.lastError().number();
            qInfo() << "Sql query last error code:"
                      << sqlQueryLastErrorNumber << ",text:"
                      << m_lastError.toStdString().c_str();
        }
    }
    else
    {
        m_lastError = "mdb not open";
        qInfo() << m_lastError;
    }
    return false;
}

bool Database::readRecords(const QString &table, const QString &fields, const MSV &filter, VMSV &records)
{
    QString condition = getCondition(filter);
    QString strSql = QString("SELECT %1 FROM `%2` %3")
            .arg(fields).arg(table).arg(condition);

    return readRecords(strSql, records);
}

bool Database::readRecords(const QString &strSql, VMSV &records)
{
    qInfo() << strSql;
    if (m_db.isOpen())
    {
        QSqlQuery sqlQuery(m_db);
        if (sqlQuery.exec(strSql))
        {
            while (sqlQuery.next())
            {
                MSV record;
                for (int i = 0; i < sqlQuery.record().count(); i++)
                {
                    QString fieldName = sqlQuery.record().fieldName(i);
                    QVariant value = sqlQuery.record().value(i);
                    record[fieldName] = value;
                }
                records.append(record);
            }
            return true;
        }
        m_lastError = sqlQuery.lastError().text();
    }
    else
    {
        m_lastError = "mdb not open";
        qInfo() << m_lastError;
    }
    return false;
}

bool Database::deleteRecord(const QString &table, const MSV &filter)
{
    QString strSql = QString("DELETE FROM %1 %2")
            .arg(table).arg(getCondition(filter));
    return executeSql(strSql);
}

bool Database::updateRecord(const QString &table, const MSV &filter, MSV &data)
{
    bool ret = false;
    QStringList fragments;
    for (MSV::const_iterator iter = data.begin(); iter != data.end(); iter++)
    {
        QString fragment = QString("`%1` = %2")
                .arg(iter.key())
                .arg(getVariantString(iter.value()));
        fragments.append(fragment);
    }
    if (fragments.size() > 0)
    {
        QString setExp = fragments.join(",");
        QString strSql = QString("UPDATE %1 SET %2 %3")
                .arg(table).arg(setExp).arg(getCondition(filter));

        ret = executeSql(strSql);
    }

    return ret;
}

bool Database::insertRecord(const QString &table, const MSV &record)
{
    QString fields = record.keys().join(",");
    QVariantList valueList = record.values();
    QStringList temp;
    for (int i = 0; i < valueList.size(); i++)
    {
        temp.append(getVariantString(valueList[i]));
    }
    QString values = temp.join(",");
    QString strSql = QString("INSERT INTO %1 (%2) VALUES(%3)")
            .arg(table).arg(fields).arg(values);
    return executeSql(strSql);
}

QString Database::getLastError()
{
    return m_lastError;
}

QString Database::getCondition(const MSV &filter)
{
    QString condition = "";
    QStringList fragments;
    for (MSV::const_iterator iter = filter.begin(); iter != filter.end(); iter++)
    {
        QString fragment = QString("`%1` = %2")
                .arg(iter.key())
                .arg(getVariantString(iter.value()));
        fragments.append(fragment);
    }
    if (fragments.size() > 0)
    {
        condition = "WHERE " + fragments.join(" AND ");
    }

    return condition;
}

QString Database::getVariantString(const QVariant &var)
{
    // 数字和函数不用加引号
    if (var.type() != QVariant::String || var.toString().endsWith("()"))
    {
        return var.toString();
    }
    else
    {
        return QString("\'%1\'").arg(var.toString());
    }
}
